﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SqlServerDriver
{
	class SqlServerImportExportInterfaceImplementation : DbViewDriver.IDatabaseExportSource 
	{
		public String ConnectionString { get; set; }
		
		private List<DbView.FieldInfo> Fields {get; set;}

		private SqlDataReader DataReader {get; set;}
		private SqlConnection HeldConnection {get; set;}
		private Object [] Output { get; set; }

		public DbView.FieldInfo[] GetSchema(String tableName)
		{
			if (Fields == null)
			{
				SqlTableSchema schema = Helper.MakeSchemaFor(tableName, ConnectionString);
				Fields = new List<DbView.FieldInfo>();
				for (int idx = 0; idx < schema.Count; ++idx)
					Fields.Add(schema[idx]);
			}
			return Fields.ToArray();
		}

		public DataTable ReadData(String tableName, String [] fields, String filterClause, String orderClause)
		{
			//String fieldList = "["+String.Join("],[", fields)+"]";
			//String sql = String.Format("SELECT {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			//if (filterClause.Length > 0)
			//    sql += " where "+filterClause;
			//if (orderClause.Length > 0)
			//    sql += " order by "+orderClause;
			String sql = MakeSql(tableName, fields, filterClause, orderClause, false);
			DataTable dt = GenericQuery(ConnectionString, sql);
			return dt;
		}

		private String MakeSql(String tableName, String [] fields, String filterClause, String orderClause, bool forCount)
		{
			String fieldList = "["+String.Join("],[", fields)+"]";
			if (forCount) fieldList = "count(*)"; // counting not fetching fields
			String sql = String.Format("SELECT {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			if (filterClause.Length > 0)
				sql += " where "+filterClause;
			if (orderClause.Length > 0 && !forCount)   // don't sort if counting. Query optimizer probably dumps this anyway
				sql += " order by "+orderClause;
			return sql;
		}

		// Large export without killing memory
		public int ReadInit(String tableName, String [] fields, String filterClause, String orderClause)
		{
			String sql = MakeSql(tableName, fields, filterClause, orderClause, true);
			DataTable dt = GenericQuery(ConnectionString, sql);
			int rowCount = Convert.ToInt32(dt.Rows[0][0]);
			// now set up the reader
			HeldConnection = new SqlConnection(ConnectionString);
			sql = MakeSql(tableName, fields, filterClause, orderClause, false);
			SqlCommand cmd  = new SqlCommand(sql, HeldConnection);
			// open the connection
			HeldConnection.Open();
			Output = null; // will be populated below
			// 1. get an instance of the SqlDataReader
			DataReader = cmd.ExecuteReader();
			return rowCount;
		}
		
		// read the data until exhausted
		public bool ReadNext(out object[] data)
		{
			if (Output == null) Output = new object[DataReader.FieldCount];
			bool readData = DataReader.Read();
			if (readData)
			{
				DataReader.GetValues(Output);
			}
			data = Output;
			return readData;
		}
		// finish
		public void ReadClose()
		{
			DataReader.Close();
			HeldConnection.Close();
		}

		public DataTable CreateTransferTable(String tableName)
		{
			DbView.FieldInfo[] fields = GetSchema(tableName);
			String fieldList="";
			foreach (DbView.FieldInfo field in fields)
			{
				if (fieldList.Length > 0) fieldList += ",";
				fieldList += field.SafeName;
			}
			String sql = String.Format("SELECT top 0 {1} from {0}", Helper.SafeTableName(tableName), fieldList);
			DataTable dt = GenericQuery(ConnectionString, sql);
			return dt;
		}
		
		// This will throw exceptions. Its up to the caller to deal with them as there
		// is not much you can do to recover here.
		public void PopulateTable(String tableName, DataTable data, bool isStaging)
		{
			String targetTableName = (isStaging) ? MakeStagingTableName(tableName) : tableName;
			
			if (true)
			{
				// Initializing an SqlBulkCopy object
				SqlBulkCopy sbc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity);
				try
				{
					// Copying data to destination
					sbc.DestinationTableName = targetTableName;
					sbc.BatchSize = 1000;    // 1000 rows at a time
					sbc.NotifyAfter = 1000;  // generate an event
					sbc.WriteToServer(data);
				}
				catch (OperationAbortedException)
				{
				}
				finally
				{
					// Closing connection and the others
					sbc.Close();
				}
			}
		}

		public void CreateStagingTable(String tableName, bool isStaging, bool create)
		{
			if (!isStaging) return; // not necessary
			if (create)
			{
				CreateStagingTable(tableName);
			}
			else
			{
				String stagingTableName = MakeStagingTableName(tableName);
				String tableScript = "if object_id('"+stagingTableName+"') is not null drop table "+ stagingTableName;
				// execute it
				GenericQuery(ConnectionString, tableScript);
			}
		}

		private String CreateStagingTable(String targetTableName)
		{
			// refactor into a new routine
			String stagingTableName = MakeStagingTableName(targetTableName);
			SqlTableSchema schema = Helper.MakeSchemaFor(targetTableName, ConnectionString);
			SqlServerScripter scripter = new SqlServerScripter(targetTableName, schema);
			String makeTableScript = scripter.ScriptCreateTable(true);
			makeTableScript = ReplaceTargetWithStaging(makeTableScript, targetTableName, stagingTableName);
			makeTableScript = "if object_id('"+stagingTableName+"') is null "+ makeTableScript;
			// execute it
			GenericQuery(ConnectionString, makeTableScript);
			// now safe to add the schema
			String[] tableParts = Helper.ParseTableName(targetTableName);
			return stagingTableName;
		}

		private String ReplaceTargetWithStaging(String script, String targetTableName, String stagingTableName)
		{
			/*
				A bit of a hack this to replace references to the table name with
				the modified staging table name.
			*/
			// Deal with schema tweak in the script
			String [] tableParts = Helper.ParseTableName(targetTableName);
			String [] stgTableParts = Helper.ParseTableName(stagingTableName);
			String seekRoot = Helper.SafeTableName(tableParts[0]);
			String replRoot = Helper.SafeTableName(stgTableParts[0]);
			
			// looking for the "[tablename] (" bit of create table [tablename] (
			String seek = seekRoot + " (";
			String repl = replRoot + " (";
			script = script.Replace(seek, repl);
			// now attend the PK_FIELD
			// remember to deal with schema stuff
			seek = seekRoot.Insert(1, "PK_");
			repl = replRoot.Insert(1, "PK_");
			script = script.Replace(seek, repl);
			return script;
		}

		// If staging update the from the staging table according to the given mode
		public void UpdateFromStaging(String tableName, String [] importedFields, DbViewDriver.CsvImportMode mode)
		{
			// At this point inject a few checks and set up a few others
			// This will hold items to be treated separately. These are fields with defaults so that 
			// null values will not be inserted instead of the default. This is better behaviour I think.
			DbView.FieldInfo [] specialList = MakeExcludedFieldList(ConnectionString, tableName, importedFields, Fields.ToArray(), mode);
			// filter the special fields from the mapped list.
			String [] groupFields = MakeGroupFieldList(importedFields, specialList);

			String stagingTableName = MakeStagingTableName(tableName);
			SqlTableSchema schema = Helper.MakeSchemaFor(tableName, ConnectionString);
			SqlServerScripter scripter = new SqlServerScripter(tableName, schema);
			// ... and this will break in the case where the table name is a reserved word, but test for this later.
			SqlScriptingHelper helper = new SqlScriptingHelper(stagingTableName, schema);
			// set the filter to prevent non-mapped fields being added to the list (think updates mainly)
			helper.SetFieldFilter(groupFields);
			// build useful SQL fragments from the configured object
			String pkeyCompare = helper.ScriptKeyCompare(tableName);
			String insertableFieldList = helper.ScriptAllButCalcFields();
			String updateableFieldList = helper.ScriptUpdate(tableName);
			// cater for the rare case where only PKeys are being updated and user has selected update mode.
			// switch to insert mode.
			if (updateableFieldList.Length == 0 && mode == DbViewDriver.CsvImportMode.Merge)
				mode = DbViewDriver.CsvImportMode.AppendOnly;
			// decide the template 
			String updateTemplate = (mode == DbViewDriver.CsvImportMode.Replace) ? SCRIPT_REPLACE :
									(mode == DbViewDriver.CsvImportMode.Merge) ? SCRIPT_MERGE :
									(mode == DbViewDriver.CsvImportMode.UpdateOnly) ? SCRIPT_UPDATE_ONLY :
									(mode == DbViewDriver.CsvImportMode.AppendOnly) ? SCRIPT_APPEND_ONLY : "";

			// {0} is target table {1} is staging {2} is a list of fields to update 
			// {3} is a pkey comparison {4} is an update statement
			String script = (mode == DbViewDriver.CsvImportMode.Replace) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList) :
							(mode == DbViewDriver.CsvImportMode.Merge) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare, updateableFieldList) :
							(mode == DbViewDriver.CsvImportMode.UpdateOnly) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare, updateableFieldList) :
							(mode == DbViewDriver.CsvImportMode.AppendOnly) ? String.Format(updateTemplate, Helper.SafeTableName(tableName), Helper.SafeTableName(stagingTableName), insertableFieldList, pkeyCompare) : "";
			// clear script if mode is right and there are no vanilla fields to update.
			if (updateableFieldList.Length == 0 && mode == DbViewDriver.CsvImportMode.UpdateOnly)
				script = "";

			// script individual updates of non-null fields to intelligently 
			foreach (DbView.FieldInfo fi in specialList)
			{
				script += String.Format(UPDATE_SPECIAL, tableName, stagingTableName, fi.Name, pkeyCompare);
			}
			// if auto-inc key wrap the script in the special code. Not if update only because no new rows are added.
			if (schema.IdentityField != null && mode != DbViewDriver.CsvImportMode.UpdateOnly)
			{
				// something better than this
				String autoIncWrapper = helper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, tableName)
							 + "{0}" + helper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, tableName);
				script = String.Format(autoIncWrapper, script);
			}
			// do it.
			GenericQuery(ConnectionString, script);
		}

		private string[] MakeGroupFieldList(string[] importedFields, DbView.FieldInfo[] specialList)
		{
			List<String> fieldList = new List<String>();
			// removed the SpecialList fields from teh importedFields.
			foreach (String field in importedFields)
			{
				bool found = false;
				foreach (DbView.FieldInfo fi in specialList)
				{
					if (fi.Name == field)
						found = true;
				}
				if (!found)
					fieldList.Add(field);
			}
			return fieldList.ToArray();
		}
		// This to find fields that will cause problems if imported directly from staging
		//
		private DbView.FieldInfo [] MakeExcludedFieldList(String connectionString, String tableName, String[] importedFields, DbView.FieldInfo [] schema, DbViewDriver.CsvImportMode mode)
		{
			List<DbView.FieldInfo> fieldList = new List<DbView.FieldInfo>();
			// include Non-null fields with and without defaults. Actually we need to 
			foreach (String field in importedFields)
			{
				foreach (DbView.FieldInfo fi in schema)
				{
					if (!fi.IsPKey)
						if (fi.Name == field && !fi.AllowsNulls)
						{
							// a. find whether there are any blank values in staging
							int nullCount = CountNullsInStaging(connectionString, tableName, field);
							// if N then do not add
							// if Y and there is a default then add to special
							// if Y and there is no default then do not add and let the operation abort.
							// if Y and mode is append only abort
							if (nullCount > 0)
							{
								if (fi.HasDefault)
									fieldList.Add(fi);
								if (mode == DbViewDriver.CsvImportMode.AppendOnly)
									throw new Exception(String.Format("Unable to continue in append only mode because import data for field [{0}] contains empty values which [{0}] cannot have.", fi.Name));
							}
						}
				}
			}
			return fieldList.ToArray();
		}

		private int CountNullsInStaging(string connectionString, string tableName, string field)
		{
			String sql = String.Format("select count({1}) as C from {0}", Helper.SafeTableName(MakeStagingTableName(tableName)), field);
			DataTable dt = GenericQuery(connectionString, sql);
			return (int)dt.Rows[0][0];
		}

		private string MakeStagingTableName(string tableName)
		{
			// Ultimate goal is to refactor to use a real temp table
			// ## is a persistent temp table.
			// also remove schemaness
			// problems with bulk copy and global temp table. Use decorated name for now
			// return String.Format("##{0}", tableName.Replace(".", "_"));
			// Check for table length?
//			return String.Format("{0}_stage", tableName.Replace(".", "_"));
			return String.Format("{0}_stage", tableName);
		}

		public int CountRows(String tableName, String filterClause)
		{
			String sql = "Select count(*) from " + Helper.SafeTableName(tableName);
			if (filterClause.Length > 0) sql += " where "+filterClause;
			DataTable dt = GenericQuery(ConnectionString, sql);
			int rowCount = (int)dt.Rows[0][0];
			return rowCount;
		}

		// helper
		private static DataTable GenericQuery(String strConnectionString, String sql)
		{
			// Create a data reading object to fill the dataset
			DataSet dset = new DataSet();
			SqlConnection sqlConn = new SqlConnection();
			try
			{
				// get the connection 
				sqlConn.ConnectionString = strConnectionString;
				sqlConn.Open();
				SqlDataAdapter da = new SqlDataAdapter(sql, sqlConn);
				// execute into something
				da.Fill(dset, "Result");
				sqlConn.Close();
				if (dset.Tables.Count == 0) return null;
				return dset.Tables[0];
			}
			catch (SqlException e)
			{
				// may not work if connection fails
				sqlConn.Close();
				String message = String.Format("Sql Error: {0}\n" +
											   "connection: {1}\n" +
												"Sql: {2}\n",
												e.Message, strConnectionString, sql);
				Exception ex = new Exception(message);
				throw ex; // abort process having logged as much as possible.
			}
		}
		// Try to keep template variables constent.
		// {0} is target table {1} is staging {2} is a list of fields to update 
		// {3} is a pkey comparison {4} is an update statement
		const String SCRIPT_REPLACE = @"
--truncate table {0} -- doesn't work with replication
delete from {0}
insert into {0} ({2}) 
     select {2} from {1}
";
		const String SCRIPT_MERGE = @"
update {0} set {4}
  from {1} where {3}
insert into {0} ({2}) 
     select {2} from {1} 
	  where not exists (select * from {0} where {3})
";
		const String SCRIPT_UPDATE_ONLY = @"
update {0} set {4}
  from {1} where {3}
-- insert into {0} ({2}) 
--     select {2} from {1} 
--	  where not exists (select * from {0} where {3})
";
		const String SCRIPT_APPEND_ONLY = @"
insert into {0} ({2}) 
     select {2} from {1} 
	  where not exists (select * from {0} where {3})
";

		const String UPDATE_SPECIAL = @"
update {0} set {2} = {1}.{2}
  from {1} where {3}
  and {1}.{2} is not null
";
	}
}
